﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

/// <summary>
/// Summary description for Conn
/// </summary>
public class Conn
{

  /// <summary>
  /// Gives you a connection which has already been opened.
  /// When using this, make sure you wrap it in a 'using' statement.
  /// </summary>
  public static SqlConnection GetOpened()
  {
    string connString = ConfigurationManager.ConnectionStrings["Main"].ConnectionString;
    SqlConnection conn = new SqlConnection(connString);
    conn.Open();
    return conn;
  }

  /// <summary>
  /// Executes a single-row query, returning the fields and values as a dictionary
  /// </summary>
  public static Dictionary<string, object> GetRowAsDict(string sql, int id)
  {
    using (SqlConnection conn = GetOpened())
    {
      SqlCommand cmd = new SqlCommand(sql, conn);
      cmd.Parameters.AddWithValue("@id", id);
      Dictionary<string, object> fields = new Dictionary<string, object>();
      SqlDataReader rdr = cmd.ExecuteReader();
      if (rdr.Read())
        for (int i = 0; i < rdr.FieldCount; i++)
          fields[rdr.GetName(i).ToLower()] = rdr[i];
      return fields;
    }
  }

  /// <summary>
  /// Insanity lies within this function...
  /// Basically, give it a table, id, and fields it'll figure out to update or insert a new row
  /// Returns the id (or new id if it was an insert)
  /// </summary>
  public static int UpdateOrInsert(string table, int id, Dictionary<string, object> values)
  {
    using (SqlConnection conn = GetOpened())
    {
      // First check if the row exists
      string existsSql = string.Format("select count(*) from {0} where id=@id", table);
      SqlCommand existsCmd = new SqlCommand(existsSql, conn);
      existsCmd.Parameters.AddWithValue("@id", id);
      bool exists = Convert.ToInt32(existsCmd.ExecuteScalar()) > 0;

      // Build the sql to suit either an update or insert
      string sql;
      if (exists)
      {
        sql = string.Format("update {0} set", table);
        bool first = true;
        foreach (string field in values.Keys)
        {
          if (!first) sql += " , ";
          sql += string.Format(" {0} = @{0}", field);
          first = false;
        }
        sql += " where id=@id;select @id";
      }
      else
      {
        sql = string.Format("insert into {0} (", table);
        bool first = true;
        foreach (string field in values.Keys)
        {
          if (!first) sql += ",";
          sql += field;
          first = false;
        }
        sql += ") values (";
        first = true;
        foreach (string field in values.Keys)
        {
          if (!first) sql += ",";
          sql += "@" + field;
          first = false;
        }
        sql += ");select scope_identity()";
      }

      // Run it
      SqlCommand cmd = new SqlCommand(sql, conn);
      foreach (string field in values.Keys)
        cmd.Parameters.AddWithValue("@" + field, values[field]);
      cmd.Parameters.AddWithValue("@id", id);
      return Convert.ToInt32(cmd.ExecuteScalar());
    }
  }

  /// <summary>
  /// Sanitise a string you want to use in a query
  /// </summary>
  public static string Sanitise(string str)
  {
    return str.Replace('\'', '?').Replace('"', '?');
  }

  /// <summary>
  /// Execute sql, passing id for @id
  /// </summary>
  public static void ExecuteNonQueryWithId(string sql, object id)
  {
    using (SqlConnection conn = GetOpened())
    {
      SqlCommand cmd = new SqlCommand(sql, conn);
      cmd.Parameters.AddWithValue("@id", id);
      cmd.ExecuteNonQuery();
    }
  }

  /// <summary>
  /// Checks to see if the current user is admin
  /// </summary>
  public static bool IsAdminUser()
  {
    string sql = "select 1 from users where email = @email and admin<>0";
    using (SqlConnection conn = GetOpened())
    {
      SqlCommand cmd = new SqlCommand(sql, conn);
      cmd.Parameters.AddWithValue("@email", HttpContext.Current.User.Identity.Name);
      return cmd.ExecuteScalar() is int;
    }
  }

}
